using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class VideoInfoService
    {
        public static bool AddVideoInfo(VideoInfo videoInfo)
        {
            string sql =
                "INSERT VideoInfo (Title, Point, Count, Location, VideoContent, Image, Author, AuthorPhoto, UploadDate, TypeID, XUHAO)" +
                "VALUES (@Title, @Point, @Count, @Location, @VideoContent, @Image, @Author, @AuthorPhoto, @UploadDate, @TypeID, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", videoInfo.Title),
				new SqlParameter("@Point", videoInfo.Point),
				new SqlParameter("@Count", videoInfo.Count),
				new SqlParameter("@Location", videoInfo.Location),
				new SqlParameter("@VideoContent", videoInfo.VideoContent),
                new SqlParameter("@Image", videoInfo.Image),
				new SqlParameter("@Author", videoInfo.Author),
				new SqlParameter("@AuthorPhoto", videoInfo.AuthorPhoto),
				new SqlParameter("@UploadDate", videoInfo.UploadDate),
                new SqlParameter("@TypeID", videoInfo.TypeID),
                new SqlParameter("@XUHAO", videoInfo.XUHAO)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static int AddVideoInfoReturnID(VideoInfo videoInfo)
        {
            string sql =
                "INSERT VideoInfo (Title, Point, Count, Location, VideoContent, Image, Author, AuthorPhoto, UploadDate, TypeID, XUHAO)" +
                "VALUES (@Title, @Point, @Count, @Location, @VideoContent, @Image, @Author, @AuthorPhoto, @UploadDate, @TypeID, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", videoInfo.Title),
				new SqlParameter("@Point", videoInfo.Point),
				new SqlParameter("@Count", videoInfo.Count),
				new SqlParameter("@Location", videoInfo.Location),
				new SqlParameter("@VideoContent", videoInfo.VideoContent),
                new SqlParameter("@Image", videoInfo.Image),
				new SqlParameter("@Author", videoInfo.Author),
				new SqlParameter("@AuthorPhoto", videoInfo.AuthorPhoto),
				new SqlParameter("@UploadDate", videoInfo.UploadDate),
                new SqlParameter("@TypeID", videoInfo.TypeID),
                new SqlParameter("@XUHAO", videoInfo.XUHAO)
			};

            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sql, para));
            return i;
        }


        public static bool DeleteVideoInfoByID(int iD)
        {
            string sql = "DELETE VideoInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyVideoInfo(VideoInfo videoInfo)
        {
            string sql =
                "UPDATE VideoInfo " +
                "SET " +
                    "Title = @Title, " +
                    "Point = @Point, " +
                    "Count = @Count, " +
                    "Location = @Location, " +
                    "VideoContent = @VideoContent, " +
                    "Image = @Image, " +
                    "Author = @Author, " +
                    "AuthorPhoto = @AuthorPhoto, " +
                    "UploadDate = @UploadDate, " +
                    "TypeID = @TypeID, " +
                    "XUHAO = @XUHAO " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", videoInfo.ID),
				new SqlParameter("@Title", videoInfo.Title),
				new SqlParameter("@Point", videoInfo.Point),
				new SqlParameter("@Count", videoInfo.Count),
				new SqlParameter("@Location", videoInfo.Location),
				new SqlParameter("@VideoContent", videoInfo.VideoContent),
                new SqlParameter("@Image", videoInfo.Image),
				new SqlParameter("@Author", videoInfo.Author),
				new SqlParameter("@AuthorPhoto", videoInfo.AuthorPhoto),
				new SqlParameter("@UploadDate", videoInfo.UploadDate),
                new SqlParameter("@TypeID", videoInfo.TypeID),
                new SqlParameter("@XUHAO", videoInfo.XUHAO)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool ModifyVideoInfoCount(int id, int count)
        {
            string sql = "UPDATE VideoInfo SET Count = @Count WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", id),
				new SqlParameter("@Count", count)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<VideoInfo> GetAllVideoInfos()
        {
            string sqlAll = "SELECT * FROM VideoInfo order by UploadDate desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosLast()
        {
            string sqlAll = "SELECT top 9 * FROM VideoInfo order by UploadDate desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosTop10()
        {
            string sqlAll = "SELECT top 10 * FROM VideoInfo order by XUHAO desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosTop10Latest()
        {
            string sqlAll = "SELECT top 10 * FROM VideoInfo order by UploadDate desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosTop10ByCount()
        {
            string sqlAll = "SELECT top 10 * FROM VideoInfo order by Count desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosTop10ByPoint()
        {
            string sqlAll = "SELECT top 10 * FROM VideoInfo order by Point desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosByTitle(string title)
        {
            string sqlAll = "SELECT * FROM VideoInfo where Title like '%" + title + "%' order by XUHAO desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static IList<VideoInfo> GetVideoInfosByAuthor(string author)
        {
            string sqlAll = "SELECT top 9 * FROM VideoInfo where Author=@Author";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Author", author)
			};
            return GetVideoInfosBySql(sqlAll, para);
        }

        public static IList<VideoInfo> GetVideoInfosByTypeID(string typeID)
        {
            string sqlAll = "SELECT * FROM VideoInfo where TypeID like '%" + typeID + "%' order by XUHAO desc,UploadDate desc";
            return GetVideoInfosBySql(sqlAll);
        }

        public static VideoInfo GetVideoInfoByID(int iD)
        {
            string sql = "SELECT * FROM VideoInfo WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            VideoInfo videoInfo = new VideoInfo();

            foreach (DataRow dr in temp.Rows)
            {
                videoInfo.ID = (int)dr["ID"];
                videoInfo.Title = (string)dr["Title"];
                videoInfo.Point = Convert.ToSingle(dr["Point"]);
                videoInfo.Count = (int)dr["Count"];
                videoInfo.Location = (string)dr["Location"];
                videoInfo.VideoContent = (string)dr["VideoContent"];
                videoInfo.Image = (string)dr["Image"];
                videoInfo.Author = (string)dr["Author"];
                videoInfo.AuthorPhoto = (string)dr["AuthorPhoto"];
                videoInfo.UploadDate = (DateTime)dr["UploadDate"];
                videoInfo.TypeID = (string)dr["TypeID"];
                videoInfo.XUHAO = (int)dr["XUHAO"];
            }
            return videoInfo;

        }


        private static IList<VideoInfo> GetVideoInfosBySql(string safeSql)
        {
            List<VideoInfo> list = new List<VideoInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    VideoInfo videoInfo = new VideoInfo();

                    videoInfo.ID = (int)dr["ID"];
                    videoInfo.Title = (string)dr["Title"];
                    videoInfo.Point = Convert.ToSingle(dr["Point"]);
                    videoInfo.Count = (int)dr["Count"];
                    videoInfo.Location = (string)dr["Location"];
                    videoInfo.VideoContent = (string)dr["VideoContent"];
                    videoInfo.Image = (string)dr["Image"];
                    videoInfo.Author = (string)dr["Author"];
                    videoInfo.AuthorPhoto = (string)dr["AuthorPhoto"];
                    videoInfo.UploadDate = (DateTime)dr["UploadDate"];
                    videoInfo.TypeID = (string)dr["TypeID"];
                    videoInfo.XUHAO = (int)dr["XUHAO"];

                    list.Add(videoInfo);
                }
            }
            return list;
        }

        private static IList<VideoInfo> GetVideoInfosBySql(string safeSql, params SqlParameter[] para)
        {
            List<VideoInfo> list = new List<VideoInfo>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    VideoInfo videoInfo = new VideoInfo();

                    videoInfo.ID = (int)dr["ID"];
                    videoInfo.Title = (string)dr["Title"];
                    videoInfo.Point = Convert.ToSingle(dr["Point"]);
                    videoInfo.Count = (int)dr["Count"];
                    videoInfo.Location = (string)dr["Location"];
                    videoInfo.VideoContent = (string)dr["VideoContent"];
                    videoInfo.Image = (string)dr["Image"];
                    videoInfo.Author = (string)dr["Author"];
                    videoInfo.AuthorPhoto = (string)dr["AuthorPhoto"];
                    videoInfo.UploadDate = (DateTime)dr["UploadDate"];
                    videoInfo.TypeID = (string)dr["TypeID"];
                    videoInfo.XUHAO = (int)dr["XUHAO"];

                    list.Add(videoInfo);
                }
            }
            return list;
        }
    }
}
